<?php
/**
 * SQL-backed OpenID stores.
 *
 * PHP versions 4 and 5
 *
 * LICENSE: See the COPYING file included in this distribution.
 *
 * @package OpenID
 * @author JanRain, Inc. <openid@janrain.com>
 * @copyright 2005-2008 Janrain, Inc.
 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache
 */

/**
 * Require the PEAR DB module because we'll need it for the SQL-based
 * stores implemented here.  We silence any errors from the inclusion
 * because it might not be present, and a user of the SQL stores may
 * supply an Auth_OpenID_DatabaseConnection instance that implements
 * its own storage.
 */
global $__Auth_OpenID_PEAR_AVAILABLE;
$__Auth_OpenID_PEAR_AVAILABLE = @include_once 'DB.php';

/**
 * @access private
 */
require_once 'Auth/OpenID/Interface.php';
require_once 'Auth/OpenID/Nonce.php';

/**
 * @access private
 */
require_once 'Auth/OpenID.php';

/**
 * @access private
 */
require_once 'Auth/OpenID/Nonce.php';

/**
 * This is the parent class for the SQL stores, which contains the
 * logic common to all of the SQL stores.
 *
 * The table names used are determined by the class variables
 * associations_table_name and nonces_table_name.  To change the name
 * of the tables used, pass new table names into the constructor.
 *
 * To create the tables with the proper schema, see the createTables
 * method.
 *
 * This class shouldn't be used directly.  Use one of its subclasses
 * instead, as those contain the code necessary to use a specific
 * database.  If you're an OpenID integrator and you'd like to create
 * an SQL-driven store that wraps an application's database
 * abstraction, be sure to create a subclass of
 * {@link Auth_OpenID_DatabaseConnection} that calls the application's
 * database abstraction calls.  Then, pass an instance of your new
 * database connection class to your SQLStore subclass constructor.
 *
 * All methods other than the constructor and createTables should be
 * considered implementation details.
 *
 * @package OpenID
 */
class Auth_OpenID_SQLStore extends Auth_OpenID_OpenIDStore {

  /**
   * This creates a new SQLStore instance.  It requires an
   * established database connection be given to it, and it allows
   * overriding the default table names.
   *
   * @param connection $connection This must be an established
   * connection to a database of the correct type for the SQLStore
   * subclass you're using.  This must either be an PEAR DB
   * connection handle or an instance of a subclass of
   * Auth_OpenID_DatabaseConnection.
   *
   * @param associations_table: This is an optional parameter to
   * specify the name of the table used for storing associations.
   * The default value is 'oid_associations'.
   *
   * @param nonces_table: This is an optional parameter to specify
   * the name of the table used for storing nonces.  The default
   * value is 'oid_nonces'.
   */
  function Auth_OpenID_SQLStore($connection, $associations_table = null, $nonces_table = null) {
    global $__Auth_OpenID_PEAR_AVAILABLE;
    
    $this->associations_table_name = "oid_associations";
    $this->nonces_table_name = "oid_nonces";
    
    // Check the connection object type to be sure it's a PEAR
    // database connection.
    if (! (is_object($connection) && (is_subclass_of($connection, 'db_common') || is_subclass_of($connection, 'auth_openid_databaseconnection')))) {
      trigger_error("Auth_OpenID_SQLStore expected PEAR connection " . "object (got " . get_class($connection) . ")", E_USER_ERROR);
      return;
    }
    
    $this->connection = $connection;
    
    // Be sure to set the fetch mode so the results are keyed on
    // column name instead of column index.  This is a PEAR
    // constant, so only try to use it if PEAR is present.  Note
    // that Auth_Openid_Databaseconnection instances need not
    // implement ::setFetchMode for this reason.
    if ($__Auth_OpenID_PEAR_AVAILABLE) {
      $this->connection->setFetchMode(DB_FETCHMODE_ASSOC);
    }
    
    if ($associations_table) {
      $this->associations_table_name = $associations_table;
    }
    
    if ($nonces_table) {
      $this->nonces_table_name = $nonces_table;
    }
    
    $this->max_nonce_age = 6 * 60 * 60;
    
    // Be sure to run the database queries with auto-commit mode
    // turned OFF, because we want every function to run in a
    // transaction, implicitly.  As a rule, methods named with a
    // leading underscore will NOT control transaction behavior.
    // Callers of these methods will worry about transactions.
    $this->connection->autoCommit(false);
    
    // Create an empty SQL strings array.
    $this->sql = array();
    
    // Call this method (which should be overridden by subclasses)
    // to populate the $this->sql array with SQL strings.
    $this->setSQL();
    
    // Verify that all required SQL statements have been set, and
    // raise an error if any expected SQL strings were either
    // absent or empty.
    list($missing, $empty) = $this->_verifySQL();
    
    if ($missing) {
      trigger_error("Expected keys in SQL query list: " . implode(", ", $missing), E_USER_ERROR);
      return;
    }
    
    if ($empty) {
      trigger_error("SQL list keys have no SQL strings: " . implode(", ", $empty), E_USER_ERROR);
      return;
    }
    
    // Add table names to queries.
    $this->_fixSQL();
  }

  function tableExists($table_name) {
    return ! $this->isError($this->connection->query(sprintf("SELECT * FROM %s LIMIT 0", $table_name)));
  }

  /**
   * Returns true if $value constitutes a database error; returns
   * false otherwise.
   */
  function isError($value) {
    return PEAR::isError($value);
  }

  /**
   * Converts a query result to a boolean.  If the result is a
   * database error according to $this->isError(), this returns
   * false; otherwise, this returns true.
   */
  function resultToBool($obj) {
    if ($this->isError($obj)) {
      return false;
    } else {
      return true;
    }
  }

  /**
   * This method should be overridden by subclasses.  This method is
   * called by the constructor to set values in $this->sql, which is
   * an array keyed on sql name.
   */
  function setSQL() {}

  /**
   * Resets the store by removing all records from the store's
   * tables.
   */
  function reset() {
    $this->connection->query(sprintf("DELETE FROM %s", $this->associations_table_name));
    
    $this->connection->query(sprintf("DELETE FROM %s", $this->nonces_table_name));
  }

  /**
   * @access private
   */
  function _verifySQL() {
    $missing = array();
    $empty = array();
    
    $required_sql_keys = array('nonce_table', 'assoc_table', 'set_assoc', 'get_assoc', 'get_assocs', 
        'remove_assoc');
    
    foreach ($required_sql_keys as $key) {
      if (! array_key_exists($key, $this->sql)) {
        $missing[] = $key;
      } else if (! $this->sql[$key]) {
        $empty[] = $key;
      }
    }
    
    return array($missing, $empty);
  }

  /**
   * @access private
   */
  function _fixSQL() {
    $replacements = array(
        array('value' => $this->nonces_table_name, 
            'keys' => array('nonce_table', 'add_nonce', 'clean_nonce')), 
        array('value' => $this->associations_table_name, 
            'keys' => array('assoc_table', 'set_assoc', 'get_assoc', 
                'get_assocs', 'remove_assoc', 
                'clean_assoc')));
    
    foreach ($replacements as $item) {
      $value = $item['value'];
      $keys = $item['keys'];
      
      foreach ($keys as $k) {
        if (is_array($this->sql[$k])) {
          foreach ($this->sql[$k] as $part_key => $part_value) {
            $this->sql[$k][$part_key] = sprintf($part_value, $value);
          }
        } else {
          $this->sql[$k] = sprintf($this->sql[$k], $value);
        }
      }
    }
  }

  function blobDecode($blob) {
    return $blob;
  }

  function blobEncode($str) {
    return $str;
  }

  function createTables() {
    $this->connection->autoCommit(true);
    $n = $this->create_nonce_table();
    $a = $this->create_assoc_table();
    $this->connection->autoCommit(false);
    
    if ($n && $a) {
      return true;
    } else {
      return false;
    }
  }

  function create_nonce_table() {
    if (! $this->tableExists($this->nonces_table_name)) {
      $r = $this->connection->query($this->sql['nonce_table']);
      return $this->resultToBool($r);
    }
    return true;
  }

  function create_assoc_table() {
    if (! $this->tableExists($this->associations_table_name)) {
      $r = $this->connection->query($this->sql['assoc_table']);
      return $this->resultToBool($r);
    }
    return true;
  }

  /**
   * @access private
   */
  function _set_assoc($server_url, $handle, $secret, $issued, $lifetime, $assoc_type) {
    return $this->connection->query($this->sql['set_assoc'], array($server_url, $handle, $secret, 
        $issued, $lifetime, $assoc_type));
  }

  function storeAssociation($server_url, $association) {
    if ($this->resultToBool($this->_set_assoc($server_url, $association->handle, $this->blobEncode($association->secret), $association->issued, $association->lifetime, $association->assoc_type))) {
      $this->connection->commit();
    } else {
      $this->connection->rollback();
    }
  }

  /**
   * @access private
   */
  function _get_assoc($server_url, $handle) {
    $result = $this->connection->getRow($this->sql['get_assoc'], array($server_url, $handle));
    if ($this->isError($result)) {
      return null;
    } else {
      return $result;
    }
  }

  /**
   * @access private
   */
  function _get_assocs($server_url) {
    $result = $this->connection->getAll($this->sql['get_assocs'], array($server_url));
    
    if ($this->isError($result)) {
      return array();
    } else {
      return $result;
    }
  }

  function removeAssociation($server_url, $handle) {
    if ($this->_get_assoc($server_url, $handle) == null) {
      return false;
    }
    
    if ($this->resultToBool($this->connection->query($this->sql['remove_assoc'], array($server_url, 
        $handle)))) {
      $this->connection->commit();
    } else {
      $this->connection->rollback();
    }
    
    return true;
  }

  function getAssociation($server_url, $handle = null) {
    if ($handle !== null) {
      $assoc = $this->_get_assoc($server_url, $handle);
      
      $assocs = array();
      if ($assoc) {
        $assocs[] = $assoc;
      }
    } else {
      $assocs = $this->_get_assocs($server_url);
    }
    
    if (! $assocs || (count($assocs) == 0)) {
      return null;
    } else {
      $associations = array();
      
      foreach ($assocs as $assoc_row) {
        $assoc = new Auth_OpenID_Association($assoc_row['handle'], $assoc_row['secret'], $assoc_row['issued'], $assoc_row['lifetime'], $assoc_row['assoc_type']);
        
        $assoc->secret = $this->blobDecode($assoc->secret);
        
        if ($assoc->getExpiresIn() == 0) {
          $this->removeAssociation($server_url, $assoc->handle);
        } else {
          $associations[] = array($assoc->issued, $assoc);
        }
      }
      
      if ($associations) {
        $issued = array();
        $assocs = array();
        foreach ($associations as $key => $assoc) {
          $issued[$key] = $assoc[0];
          $assocs[$key] = $assoc[1];
        }
        
        array_multisort($issued, SORT_DESC, $assocs, SORT_DESC, $associations);
        
        // return the most recently issued one.
        list($issued, $assoc) = $associations[0];
        return $assoc;
      } else {
        return null;
      }
    }
  }

  /**
   * @access private
   */
  function _add_nonce($server_url, $timestamp, $salt) {
    $sql = $this->sql['add_nonce'];
    $result = $this->connection->query($sql, array($server_url, $timestamp, $salt));
    if ($this->isError($result)) {
      $this->connection->rollback();
    } else {
      $this->connection->commit();
    }
    return $this->resultToBool($result);
  }

  function useNonce($server_url, $timestamp, $salt) {
    global $Auth_OpenID_SKEW;
    
    if (abs($timestamp - time()) > $Auth_OpenID_SKEW) {
      return False;
    }
    
    return $this->_add_nonce($server_url, $timestamp, $salt);
  }

  /**
   * "Octifies" a binary string by returning a string with escaped
   * octal bytes.  This is used for preparing binary data for
   * PostgreSQL BYTEA fields.
   *
   * @access private
   */
  function _octify($str) {
    $result = "";
    for ($i = 0; $i < Auth_OpenID::bytes($str); $i ++) {
      $ch = substr($str, $i, 1);
      if ($ch == "\\") {
        $result .= "\\\\\\\\";
      } else if (ord($ch) == 0) {
        $result .= "\\\\000";
      } else {
        $result .= "\\" . strval(decoct(ord($ch)));
      }
    }
    return $result;
  }

  /**
   * "Unoctifies" octal-escaped data from PostgreSQL and returns the
   * resulting ASCII (possibly binary) string.
   *
   * @access private
   */
  function _unoctify($str) {
    $result = "";
    $i = 0;
    while ($i < strlen($str)) {
      $char = $str[$i];
      if ($char == "\\") {
        // Look to see if the next char is a backslash and
        // append it.
        if ($str[$i + 1] != "\\") {
          $octal_digits = substr($str, $i + 1, 3);
          $dec = octdec($octal_digits);
          $char = chr($dec);
          $i += 4;
        } else {
          $char = "\\";
          $i += 2;
        }
      } else {
        $i += 1;
      }
      
      $result .= $char;
    }
    
    return $result;
  }

  function cleanupNonces() {
    global $Auth_OpenID_SKEW;
    $v = time() - $Auth_OpenID_SKEW;
    
    $this->connection->query($this->sql['clean_nonce'], array($v));
    $num = $this->connection->affectedRows();
    $this->connection->commit();
    return $num;
  }

  function cleanupAssociations() {
    $this->connection->query($this->sql['clean_assoc'], array(time()));
    $num = $this->connection->affectedRows();
    $this->connection->commit();
    return $num;
  }
}
